Create Backups & Restoring Database In MSSQL [ASP.NET / C#]

This article shows how to backup and restore databases in MSSQL from the application (Win Forms / ASP.NET) using scripts. This is done using executing sql queries so it is all done from sql server not from the application side. So when  restoring and when taking backup from the database all the file be created and taken from the sql server side not from the application server or from the client machine.

The following codes show how to take a backup from a database.

The SQL query used:

BACKUP DATABASE @DBName TO DISK = @PATH

*Make sure that you don’t use SqlTransaction to execute this query because it does not allow you to execute within a transaction.

public void BackupDataBase(string strPath, string strDBName, Connection connection)
{
try
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = ” BACKUP DATABASE @DBName TO DISK = @PATH “;
sqlCommand.CommandTimeout = 86400000;
sqlCommand.Parameters.AddWithValue(“@DBName”, strDBName);
sqlCommand.Parameters.AddWithValue(“@PATH”, strPath);
sqlCommand.Connection = connection.GetConnection();
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
}

The following codes show how to restore a .bak file to a database.

First, before restoring a .bak file it is better to see whether it is corrupted or not. Otherwise, if you restore it to a live database without checking you will be getting into big trouble. So it is good to check .bak file before restoring. The following code will show how to do that.

The SQL query used:

RESTORE VERIFYONLY FROM DISK = @PATH

public int VerifyBakFile(string strPath, Connection connection)
{
try
{
int result;
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = “RESTORE VERIFYONLY FROM DISK = @PATH”;
sqlCommand.CommandTimeout = 86400000;
sqlCommand.Parameters.AddWithValue(“@PATH”, strPath);
sqlCommand.Connection = connection.GetConnection();

result = sqlCommand.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
return -99;
}
}

After verifying the .bak file you can restore it to the database. Here it is done using a SQL script. when resorting the database make sure that you are not restoring the .bak file to the same database your going to run the script to restore the database. So it is good to create the connection master database and run the SQL script. Before restoring the database we have to kick all the users who are using the database otherwise SQL server won’t allow you to restore the database. Following code can be used to remove all the users :

This will only allow one user to use the database.

ALTER DATABASE ” + strDBName + ” SET Single_User WITH Rollback Immediate;

After making it to the single-mode you can restore the database.

RESTORE DATABASE ” +strDBName + ” FROM DISK = @PATH WITH REPLACE ;

After executing restore script make sure you turn it back to multi-user mode.

ALTER DATABASE ” + strDBName + ” SET Multi_User ;

public void RestoreDataBase(string strPath, string strDBName, string strConnection)
{
SqlConnection oSqlConnection = oSqlConnection = new SqlConnection();
try
{
SqlConnectionStringBuilder oSqlConnectionStringBuilder = new SqlConnectionStringBuilder(strConnection);
oSqlConnectionStringBuilder.InitialCatalog = “master”;
oSqlConnection.ConnectionString = oSqlConnectionStringBuilder.ConnectionString;
oSqlConnection.Open();

SqlCommand sqlCommand = new SqlCommand();

sqlCommand.CommandText = “ALTER DATABASE ” + strDBName + ” SET Single_User WITH Rollback Immediate ; RESTORE DATABASE ” +
strDBName + ” FROM DISK = @PATH WITH REPLACE ; ALTER DATABASE ” + strDBName + ” SET Multi_User ;”;
sqlCommand.CommandTimeout = 86400000;
sqlCommand.Parameters.AddWithValue(“@DBName”, strDBName);
sqlCommand.Parameters.AddWithValue(“@PATH”, strPath);
sqlCommand.Connection = oSqlConnection;
sqlCommand.ExecuteNonQuery();
oSqlConnection.Close();
}
catch (Exception ex)
{
oSqlConnection.Close();
throw ex;
}
}